﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SQLite;


namespace Keeyo.DataHelper.QueryAdapter
{
    public class SQLiteAdapter<T> : IQuery<T> where T : Entity, new()
    {
        #region IQuery<T> 辅助方法

        Type entityType = typeof(T);
        SQLiteAdapter _SQLiteHelper = new SQLiteAdapter();
        SQLiteAdapter SQLiteHelper
        {
            get
            {
                _SQLiteHelper.ConnectionString = ConnectionString;
                return _SQLiteHelper;
            }
        }
        private string _exclude;
        private string[] _excludeSplit = new string[0];
        private string _include;
        private string[] _includeSplit = new string[0];

        string GetTableName()
        {
            string TableName = null;
            NameInDatabaseAttribute[] NameInDatabase = entityType.GetCustomAttributes(typeof(NameInDatabaseAttribute), true) as NameInDatabaseAttribute[];
            if (NameInDatabase.Length == 0)
            {
                TableName = entityType.Name;
                if (TableName.EndsWith("Entity", StringComparison.OrdinalIgnoreCase))
                {
                    TableName = TableName.Substring(0, TableName.Length - 6);
                }
            }
            else
            {
                TableName = NameInDatabase[0].Name;
            }
            return TableName;
        }
        string GetPropertyName(PropertyInfo Property)
        {
            string PropertyName = null;
            NameInDatabaseAttribute NameInDatabase = Attribute.GetCustomAttribute(Property, typeof(NameInDatabaseAttribute), true) as NameInDatabaseAttribute;
            if (NameInDatabase == null)
            {
                PropertyName = Property.Name;
            }
            else
            {
                PropertyName = NameInDatabase.Name;
            }
            return PropertyName;
        }
        string[] SplitString(string original)
        {
            if (String.IsNullOrEmpty(original))
            {
                return new string[0];
            }

            var split = from piece in original.Split(',')
                        let trimmed = piece.Trim()
                        where !String.IsNullOrEmpty(trimmed)
                        select trimmed;
            return split.ToArray();
        }
        bool IsPropertyAllowed(string propertyName, string[] includeProperties, string[] excludeProperties)
        {
            // We allow a property to be bound if its both in the include list AND not in the exclude list.
            // An empty include list implies all properties are allowed.
            // An empty exclude list implies no properties are disallowed.
            bool includeProperty = (includeProperties == null) || (includeProperties.Length == 0) || includeProperties.Contains(propertyName, StringComparer.OrdinalIgnoreCase);
            bool excludeProperty = (excludeProperties != null) && excludeProperties.Contains(propertyName, StringComparer.OrdinalIgnoreCase);
            return includeProperty && !excludeProperty;
        }

        List<PropertyInfo> GetAllowedProperties(PropertyInfo[] Properties, SQLMethod SQLMethod)
        {
            List<PropertyInfo> AllowedProperties = new List<PropertyInfo>();
            if (Properties != null && Properties.Length > 0)
            {
                foreach (PropertyInfo property in Properties)
                {
                    if (IsPropertyAllowed(property, SQLMethod))
                    {
                        AllowedProperties.Add(property);
                    }
                }
            }
            return AllowedProperties;
        }
        bool IsPropertyAllowed(PropertyInfo Property, SQLMethod SQLMethod)
        {
            EnableAttribute Enable = null;
            switch (SQLMethod)
            {
                case SQLMethod.Select:
                    Enable = Attribute.GetCustomAttribute(Property, typeof(SelectableAttribute), true) as EnableAttribute;
                    break;
                case SQLMethod.Insert:
                    Enable = Attribute.GetCustomAttribute(Property, typeof(InsertableAttribute), true) as EnableAttribute;
                    break;
                case SQLMethod.Update:
                    Enable = Attribute.GetCustomAttribute(Property, typeof(UpdatableAttribute), true) as EnableAttribute;
                    break;
            }
            if (Enable == null)
            {
                return IsPropertyAllowed(Property.Name, _includeSplit, _excludeSplit);
            }
            return Enable.Enable;
        }

        string GetSelectString(string[] Fields, string TableName)
        {
            StringBuilder SelectString = new StringBuilder("SELECT ");
            SelectString.Append("[");
            SelectString.Append(string.Join("],[", Fields));
            SelectString.Append("] FROM [").Append(TableName).Append("]");
            return SelectString.ToString();
        }
        string GetSelectString(PropertyInfo[] Properties, string WhereString, string OrderString, int Page, int PageSize)
        {
            List<PropertyInfo> properties = Properties.ToList();
            string[] Fields = properties.Select(x => GetPropertyName(x)).ToArray();
            if (string.IsNullOrEmpty(OrderString))
            {
                PropertyInfo PropertyID = entityType.GetProperty("ID") ?? properties[0];
                OrderString = GetPropertyName(PropertyID);
            }
            StringBuilder SelectString = new StringBuilder(" SELECT [");
            SelectString.Append(string.Join("],[", Fields));
            SelectString.Append("] FROM [").Append(GetTableName()).Append("] ");
            if (!string.IsNullOrEmpty(WhereString))
            {
                SelectString.Append(" WHERE ").Append(WhereString);
            }
            if (!string.IsNullOrEmpty(OrderString))
            {
                SelectString.Append(" ORDER BY ").Append(OrderString);
            }
            SelectString.Append(" LIMIT ").Append((Page - 1) * PageSize).Append(" , ").Append(PageSize);
            return SelectString.ToString();
        }
        string GetSelectString(PropertyInfo[] Properties, string WhereString, string OrderString)
        {
            List<PropertyInfo> properties = Properties.ToList();
            string[] Fields = properties.Select(x => GetPropertyName(x)).ToArray();
            if (string.IsNullOrEmpty(OrderString))
            {
                PropertyInfo PropertyID = entityType.GetProperty("ID") ?? properties[0];
                OrderString = GetPropertyName(PropertyID);
            }
            StringBuilder SelectString = new StringBuilder(" SELECT [");
            SelectString.Append(string.Join("],[", Fields));
            SelectString.Append("] FROM [").Append(GetTableName()).Append("] ");
            if (!string.IsNullOrEmpty(WhereString))
            {
                SelectString.Append(" WHERE ").Append(WhereString);
            }
            if (!string.IsNullOrEmpty(OrderString))
            {
                SelectString.Append(" ORDER BY ").Append(OrderString);
            }
            return SelectString.ToString();
        }
        string GetInsertString(string[] Fields, string TableName)
        {
            StringBuilder SelectString = new StringBuilder("INSERT INTO  ");
            SelectString.Append("[").Append(TableName).Append("] ");
            SelectString.Append(" ([");
            SelectString.Append(string.Join("],[", Fields));
            SelectString.Append("])  VALUES  (@");
            SelectString.Append(string.Join(",@", Fields));
            SelectString.Append(")");
            return SelectString.ToString();
        }
        string GetUpdateString(string[] Fields, string TableName)
        {
            StringBuilder SelectString = new StringBuilder("UPDATE  ");
            SelectString.Append("[").Append(TableName).Append("]  SET");

            if (Fields.Length == 1)
            {
                SelectString.Append("  [").Append(Fields[0]).Append("]=@").Append(Fields[0]);
            }
            else
            {
                int length = Fields.Length - 1;
                for (int i = 0; i < length; i++)
                {
                    SelectString.Append("  [").Append(Fields[i]).Append("]=@").Append(Fields[i]).Append(", ");
                }
                SelectString.Append(" [").Append(Fields[length]).Append("]=@").Append(Fields[length]);
            }

            return SelectString.ToString();
        }

        #endregion

        #region IQuery<T> 成员

        public string ConnectionString { get; set; }
        public string Exclude
        {
            get
            {
                return _exclude ?? String.Empty;
            }
            set
            {
                _exclude = value;
                _excludeSplit = SplitString(value);
            }
        }
        public string Include
        {
            get
            {
                return _include ?? String.Empty;
            }
            set
            {
                _include = value;
                _includeSplit = SplitString(value);
            }
        }

        public T SelectByID(object ID)
        {
            PropertyInfo propertyID = entityType.GetProperty("ID");
            string IDName = GetPropertyName(propertyID);
            string WhereString = "  [" + IDName + "]=@ID";
            List<SQLiteParameter> p = new List<SQLiteParameter>();
            p.Add(new SQLiteParameter("@ID", ID));
            return Select(WhereString, null, p.ToArray());
        }
        public T Select(string WhereString)
        {
            return Select(WhereString, null, null);
        }
        public T Select(string WhereString, IDataParameter[] DataParameter)
        {
            return Select(WhereString, null, DataParameter);
        }
        public T Select(string WhereString, string OrderString, IDataParameter[] DataParameter)
        {
            List<PropertyInfo> properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Select);
            StringBuilder SQLText = new StringBuilder(GetSelectString(properties.Select(x => GetPropertyName(x)).ToArray(), GetTableName()));
            if (!string.IsNullOrEmpty(WhereString))
            {
                SQLText.Append(" WHERE ").Append(WhereString);
            }
            if (!string.IsNullOrEmpty(OrderString))
            {
                SQLText.Append(" ORDER BY ").Append(WhereString);
            }
            return SQLiteHelper.ExecuteReader(SQLText.ToString(), DataParameter).ToEntity<T>(properties.ToArray());
        }

        public IList<T> SelectAll()
        {
            return SelectAll(null, null);
        }
        public IList<T> SelectAll(string WhereString, string OrderString)
        {
            return SelectAll(WhereString, OrderString, null);
        }
        public IList<T> SelectAll(string WhereString, string OrderString, IDataParameter[] DataParameter)
        {
            PropertyInfo[] properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Select).ToArray();
            string SQLText = GetSelectString(properties, WhereString, OrderString);
            return SQLiteHelper.ExecuteReader(SQLText, DataParameter).ToListEntity<T>(properties);
        }
        public IList<T> SelectAll(int TopN, string WhereString, string OrderString, IDataParameter[] DataParameter)
        {
            PropertyInfo[] properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Select).ToArray();
            string SQLText = GetSelectString(properties, WhereString, OrderString, 1, TopN);
            return SQLiteHelper.ExecuteReader(SQLText, DataParameter).ToListEntity<T>(properties);
        }

        public IPagingList<T> SelectAll(string WhereString, string OrderString, int Page, int PageSize)
        {
            return SelectAll(WhereString, OrderString, null, Page, PageSize);
        }
        public IPagingList<T> SelectAll(string WhereString, string OrderString, IDataParameter[] DataParameter, int Page, int PageSize)
        {
            int TotalItems = GetCount(WhereString, DataParameter);
            PropertyInfo[] properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Select).ToArray();
            string SQLText = GetSelectString(properties, WhereString, OrderString, Page, PageSize);
            return SQLiteHelper.ExecuteReader(SQLText, DataParameter).ToListEntity<T>(properties).ToPagingList(Page, PageSize, TotalItems);
        }

        public int GetCount()
        {
            return GetCount(null);
        }
        public int GetCount(string WhereString)
        {
            return GetCount(WhereString, null);
        }
        public int GetCount(string WhereString, IDataParameter[] DataParameter)
        {
            StringBuilder SQLText = new StringBuilder("SELECT COUNT(*) FROM");
            SQLText.Append("  [").Append(GetTableName()).Append("] ");
            if (!string.IsNullOrEmpty(WhereString))
            {
                SQLText.Append(" WHERE ").Append(WhereString);
            }
            return Convert.ToInt32(SQLiteHelper.ExecuteScalar(SQLText.ToString(), DataParameter));
        }

        public int Insert(T Entity)
        {
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> Fields = new List<string>();
            foreach (PropertyInfo property in entityType.GetProperties())
            {
                if (IsPropertyAllowed(property, SQLMethod.Insert))
                {
                    string propertyName = GetPropertyName(property);
                    Fields.Add(propertyName);
                    SQLiteParameter p = new SQLiteParameter();
                    p.ParameterName = "@" + propertyName;
                    object temo = property.GetValue(Entity, null);
                    if (temo is Entity)
                    {
                        p.Value = property.PropertyType.GetProperty("ID").GetValue(property.GetValue(Entity, null), null);
                    }
                    else
                    {
                        p.Value = temo;
                    }
                    parameters.Add(p);
                }
            }
            string SQLText = GetInsertString(Fields.ToArray(), GetTableName());
            return SQLiteHelper.ExecuteNonQuery(SQLText, parameters.ToArray());
        }

        public int Update(T Entity)
        {
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> Fields = new List<string>();
            foreach (PropertyInfo property in entityType.GetProperties())
            {
                if (IsPropertyAllowed(property, SQLMethod.Update))
                {
                    if (property.Name == "ID") continue;

                    string propertyName = GetPropertyName(property);
                    Fields.Add(propertyName);
                    SQLiteParameter p = new SQLiteParameter();
                    p.ParameterName = "@" + propertyName;
                    object temo = property.GetValue(Entity, null);
                    if (temo is Entity)
                    {
                        p.Value = property.PropertyType.GetProperty("ID").GetValue(property.GetValue(Entity, null), null);
                    }
                    else
                    {
                        p.Value = temo;
                    }
                    parameters.Add(p);
                }
            }

            PropertyInfo propertyID = entityType.GetProperty("ID");
            string IDName = GetPropertyName(propertyID);
            SQLiteParameter IDp = new SQLiteParameter();
            IDp.ParameterName = "@" + IDName;
            IDp.Value = propertyID.GetValue(Entity, null);
            parameters.Add(IDp);
            string SQLText = GetUpdateString(Fields.ToArray(), GetTableName()) + " WHERE [" + IDName + "]=@" + IDName;
            return SQLiteHelper.ExecuteNonQuery(SQLText, parameters.ToArray());
        }
        public int Update(T Entity, string WhereString, IDataParameter[] DataParameter)
        {
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> Fields = new List<string>();
            foreach (PropertyInfo property in entityType.GetProperties())
            {
                if (IsPropertyAllowed(property, SQLMethod.Update))
                {
                    string propertyName = GetPropertyName(property);
                    Fields.Add(propertyName);
                    SQLiteParameter p = new SQLiteParameter();
                    p.ParameterName = "@" + propertyName;
                    object temo = property.GetValue(Entity, null);
                    if (temo is Entity)
                    {
                        p.Value = property.PropertyType.GetProperty("ID").GetValue(property.GetValue(Entity, null), null);
                    }
                    else
                    {
                        p.Value = temo;
                    }
                    parameters.Add(p);
                }
            }
            string SQLText = GetUpdateString(Fields.ToArray(), GetTableName());
            if (!string.IsNullOrEmpty(WhereString))
            {
                SQLText += " WHERE " + WhereString;
                if (DataParameter != null)
                    parameters.AddRange((SQLiteParameter[])DataParameter);
            }
            return SQLiteHelper.ExecuteNonQuery(SQLText, parameters.ToArray());
        }

        public int Delete(object ID)
        {
            if (ID == null)
            {
                return 0;
            }
            string IDName = GetPropertyName(entityType.GetProperty("ID"));
            string WhereString = "[" + IDName + "] =@ID";
            List<SQLiteParameter> p = new List<SQLiteParameter>();
            p.Add(new SQLiteParameter("@ID", ID));
            return Delete(WhereString, p.ToArray());
        }
        public int Delete<K>(K[] IDs)
        {
            if (IDs == null || IDs.Length == 0)
            {
                return 0;
            }
            List<SQLiteParameter> p = new List<SQLiteParameter>();
            for (int i = 0; i < IDs.Length; i++)
            {
                p.Add(new SQLiteParameter()
                {
                    ParameterName = "ID" + i.ToString(),
                    Value = IDs[i]
                });
            }
            string WhereString = "[" + GetPropertyName(entityType.GetProperty("ID")) + "] IN";
            WhereString += "(@" + string.Join("@,@", p.Select(x => x.ParameterName).ToArray()) + ")";
            return Delete(WhereString, p.ToArray());
        }
        public int Delete(string WhereString, IDataParameter[] DataParameter)
        {
            StringBuilder SQLText = new StringBuilder("DELETE  FROM");
            SQLText.Append("  [").Append(GetTableName()).Append("] ");
            if (!string.IsNullOrEmpty(WhereString))
            {
                SQLText.Append(" WHERE ").Append(WhereString);
            }
            return SQLiteHelper.ExecuteNonQuery(SQLText.ToString(), DataParameter);
        }

        public string GetSelectString()
        {
            List<PropertyInfo> properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Select);
            return GetSelectString(properties.Select(x => GetPropertyName(x)).ToArray(), GetTableName());
        }
        public string GetSelectString(int TopN)
        {
            throw new NotImplementedException();
        }

        public string GetInsertString()
        {
            List<PropertyInfo> properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Insert);
            return GetInsertString(properties.Select(x => GetPropertyName(x)).ToArray(), GetTableName()); ;
        }

        public string GetUpdateString()
        {
            List<PropertyInfo> properties = GetAllowedProperties(entityType.GetProperties(), SQLMethod.Update);
            return GetUpdateString(properties.Select(x => GetPropertyName(x)).ToArray(), GetTableName()); ;
        }

        #endregion

        enum SQLMethod
        {
            Select = 0,
            Insert = 1,
            Update = 2,
            Delete = 3
        }
    }

    public class SQLiteAdapter : IQuery
    {
        #region IQuery 成员

        public string ConnectionString { get; set; }

        public IDbConnection GetDbConnection()
        {
            return new SQLiteConnection(ConnectionString);
        }

        public IDataReader ExecuteReader(string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            return ExecuteReader(null, SQLText, DataParameter, CommandType);
        }

        public IDataReader ExecuteReader(string SQLText, IDataParameter[] DataParameter)
        {
            return ExecuteReader(null, SQLText, DataParameter, CommandType.Text);
        }

        public IDataReader ExecuteReader(string SQLText)
        {
            return ExecuteReader(null, SQLText, null, CommandType.Text);
        }

        public int ExecuteNonQuery(string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            int i = 0;
            using (SQLiteConnection conn = GetDbConnection() as SQLiteConnection)
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                if (DataParameter != null) cmd.Parameters.AddRange(DataParameter);
                cmd.CommandText = SQLText;
                cmd.CommandType = CommandType;
                i = cmd.ExecuteNonQuery();
            }
            return i;
        }

        public int ExecuteNonQuery(string SQLText, IDataParameter[] DataParameter)
        {
            return ExecuteNonQuery(SQLText, DataParameter as SQLiteParameter[], CommandType.Text);
        }

        public int ExecuteNonQuery(string SQLText)
        {
            return ExecuteNonQuery(SQLText, null, CommandType.Text);
        }

        public object ExecuteScalar(string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            object o = null;
            using (SQLiteConnection conn = GetDbConnection() as SQLiteConnection)
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                if (DataParameter != null) cmd.Parameters.AddRange(DataParameter);
                cmd.CommandText = SQLText;
                cmd.CommandType = CommandType;
                o = cmd.ExecuteScalar();
            }
            return o;
        }

        public object ExecuteScalar(string SQLText, IDataParameter[] DataParameter)
        {
            return ExecuteScalar(SQLText, DataParameter as SQLiteParameter[], CommandType.Text);
        }

        public object ExecuteScalar(string SQLText)
        {
            return ExecuteScalar(SQLText, null, CommandType.Text);
        }

        #endregion


        public IDataReader ExecuteReader(IDbTransaction Transaction, string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            SQLiteDataReader rd = null;
            SQLiteConnection connection = new SQLiteConnection(ConnectionString);
            try
            {
                connection.Open();
                SQLiteCommand cmd = connection.CreateCommand();
                if (DataParameter != null)
                {
                    foreach (var p in DataParameter)
                    {
                        cmd.Parameters.Add((SQLiteParameter)p);
                    }
                }
                cmd.CommandText = SQLText;
                cmd.CommandType = CommandType;
                cmd.Transaction = Transaction as SQLiteTransaction;
                rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return rd;
            }
            catch
            {
                if (connection != null)
                {
                    connection.Close();
                }
                throw;
            }
        }

        public int ExecuteNonQuery(IDbTransaction Transaction, string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            throw new NotImplementedException();
        }

        public object ExecuteScalar(IDbTransaction Transaction, string SQLText, IDataParameter[] DataParameter, CommandType CommandType)
        {
            throw new NotImplementedException();
        }
    }
}
